Getting MySQL metadata with Perl

Course- MySQL >

Metadata is information about the data in the database. Metadata in MySQL contains information about the tables and columns, in which we store data. Number of rows affected by an SQL statement is a metadata. Number of rows and columns returned in a result set belong to metadata as well.

Method name	Description
`column_info()`	Provides information about columns
`table_info()`	Provides information about tables
`primary_key_info()`	Provides information about primary keys in tables
`foreign_key_info()`	Provides information about foreign keys in tables

The above table lists four Perl DBI methods, which are used to retrieve metadata.

#!/usr/bin/perl

use strict;
use DBI;

my $dbh = DBI->connect(          
    "dbi:mysql:dbname=mydb", 
    "user12",                          
    "34klq*",                          
    { RaiseError => 1 },         
) or die $DBI::errstr;

my $sth = $dbh->primary_key_info(undef, "mydb", "Cars");
my @ary = $sth->fetchrow_array();
      
print join(" ", @ary), "\n";

$sth->finish();
$dbh->disconnect();

In the first example, we will find out information about a primary key in the Cars table.

my $sth = $dbh->primary_key_info(undef, "main", "Cars");

The primary_key_info() returns an active statement handle that can be used to fetch information about columns that make up the primary key for a table.

my @ary = $sth->fetchrow_array();

From the statement handle, we retrieve the information.

$ ./pk_info.pl
 mydb Cars Id 1 PRIMARY

From the output we can see that there is a primary key in the Cars table. The primary key is the first column, named Id.

Next we will print all rows from the Cars table with their column names.

#!/usr/bin/perl

use strict;
use DBI;

my $dbh = DBI->connect(          
    "dbi:mysql:dbname=mydb", 
    "user12",                          
    "34klq*",                          
    { RaiseError => 1 },         
) or die $DBI::errstr;

my $sth = $dbh->prepare( "SELECT * FROM Cars LIMIT 8" );  
$sth->execute();

my $headers = $sth->{NAME};

my ($id, $name, $price) = @$headers;
printf  "%s %-10s %s\n", $id, $name, $price;

my $row;
while($row = $sth->fetchrow_hashref()) {
    printf "%2d %-10s %d\n", $row->{Id}, $row->{Name}, $row->{Price};
}

$sth->finish();
$dbh->disconnect();

We print the contents of the Cars table to the console. Now, we include the names of the columns too. The records are aligned with the column names.

my $headers = $sth->{NAME};

We get the column names from the statement object.

my ($id, $name, $price) = @$headers;
printf "%s %-10s %s\n", $id, $name, $price;

The column names are printed to the console. We apply some formatting with the printf function.

my $row;
while($row = $sth->fetchrow_hashref()) {
    printf "%2d %-10s %d\n", $row->{Id}, $row->{Name}, $row->{Price};
}

The data is retrieved, formatted, and printed to the terminal.

$ ./column_names.pl
Id Name       Price
 1 Audi       52642
 2 Mercedes   57127
 3 Skoda      9000
 4 Volvo      29000
 5 Bentley    350000
 6 Citroen    21000
 7 Hummer     41400
 8 Volkswagen 21601

Output of the column_names.pl script.

In our last example related to metadata, we will list all tables in the test.db database.

#!/usr/bin/perl

use strict;
use DBI;

my $dbh = DBI->connect(          
    "dbi:mysql:dbname=mydb", 
    "user12",                          
    "34klq*",                          
    { RaiseError => 1 },         
) or die $DBI::errstr;

my @tables = $dbh->tables(); 

foreach my $table ( @tables ) {
     print "Table: $table\n"; 
}

$dbh->disconnect();

The code example prints all available tables in the current database to the terminal.

my @tables = $dbh->tables();

The table names are retrieved with the tables() method.

$ ./list_tables.pl
Table: `mydb`.`Cars`
Table: `mydb`.`Friends`
Table: `mydb`.`Images`

These were the tables on our system.

In this part of the MySQL Perl tutorial, we have worked with database metadata.

PRV MENU NEXT

Getting MySQL metadata with Perl

Latest Post